`:top

`!Set operations`! in `F33f`_`[SQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL]`_`f is a type of operations which allow the results of multiple queries to be combined into a single `F33f`_`[result set`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Result_set]`_`f.`:cite-ref-1[`F5bf`_`[1`#cite-note-1]`_`f]

Set operators in SQL include `B100`F9d9UNION`f`b, `B100`F9d9INTERSECT`f`b, and `B100`F9d9EXCEPT`f`b, which `F33f`_`[mathematically`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Set_theory]`_`f correspond to the concepts of `F33f`_`[union`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Union_(set_theory)]`_`f, `F33f`_`[intersection`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Intersection_(set_theory)]`_`f and `F33f`_`[set difference`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Set_difference]`_`f.

>>Contents

• `F0af`_`[UNION operator`#union-operator]`_`f

• `F0af`_`[Examples`#examples]`_`f

• `F0af`_`[INTERSECT operator`#intersect-operator]`_`f

• `F0af`_`[Example`#example]`_`f

• `F0af`_`[EXCEPT operator`#except-operator]`_`f

• `F0af`_`[Example`#example]`_`f

• `F0af`_`[Example`#example]`_`f

• `F0af`_`[See also`#see-also]`_`f

• `F0af`_`[References`#references]`_`f

• `F0af`_`[External links`#external-links]`_`f

-─

>>UNION operator

In `F33f`_`[SQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL]`_`f the `!`B100`F9d9UNION`f`b`! clause combines the results of two SQL queries into a single `F33f`_`[table`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Table_(database)]`_`f of all matching `F33f`_`[rows`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Row_(database)]`_`f. The two queries must result in the same number of `F33f`_`[columns`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Column_(database)]`_`f and compatible `F33f`_`[data types`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Data_type]`_`f in order to unite. Any duplicate records are automatically removed unless `B100`F9d9UNION ALL`f`b is used.

`B100`F9d9UNION`f`b can be useful in `F33f`_`[data warehouse`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Data_warehouse]`_`f applications where tables are not perfectly `F33f`_`[normalized`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Database_normalization]`_`f.`:cite-ref-2[`F5bf`_`[2`#cite-note-2]`_`f] A simple example would be a database having tables `B100`F9d9sales2005`f`b and `B100`F9d9sales2006`f`b that have identical structures but are separated because of performance considerations. A `B100`F9d9UNION`f`b query could combine results from both tables.

Note that `B100`F9d9UNION ALL`f`b does not guarantee the order of rows. Rows from the second operand may appear before, after, or mixed with rows from the first operand. In situations where a specific order is desired, `B100`F9d9ORDER BY`f`b must be used.

Note that `B100`F9d9UNION ALL`f`b may be much faster than plain `B100`F9d9UNION`f`b.

>>>Examples

Given these two tables:

`t

| person | amount |

|---|---|

| Joe | 1000 |

| Alex | 2000 |

| Bob | 5000 |

`t

`t

| person | amount |

|---|---|

| Joe | 2000 |

| Alex | 2000 |

| Zach | 35000 |

`t

Executing this statement:

`B100`F9d9SELECT * FROM sales2005`f`b

`B100`F9d9UNION`f`b

`B100`F9d9SELECT * FROM sales2006;`f`b

yields this result set, though the order of the rows can vary because no `B100`F9d9ORDER BY`f`b clause was supplied:

`t

| person | amount |

|---|---|

| Joe | 1000 |

| Alex | 2000 |

| Bob | 5000 |

| Joe | 2000 |

| Zach | 35000 |

`t

Note that there are two rows for Joe because those rows are distinct across their columns. There is only one row for Alex because those rows are not distinct for both columns.

`B100`F9d9UNION ALL`f`b gives different results, because it will not eliminate duplicates. Executing this statement:

`B100`F9d9SELECT * FROM sales2005`f`b

`B100`F9d9UNION ALL`f`b

`B100`F9d9SELECT * FROM sales2006;`f`b

would give these results, again allowing variance for the lack of an `B100`F9d9ORDER BY`f`b statement:

`t

| person | amount |

|---|---|

| Joe | 1000 |

| Joe | 2000 |

| Alex | 2000 |

| Alex | 2000 |

| Bob | 5000 |

| Zach | 35000 |

`t

The discussion of `F33f`_`[full outer joins`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Join_(SQL)]`_`f also has an example that uses `B100`F9d9UNION`f`b.

>>INTERSECT operator

The SQL `B100`F9d9INTERSECT`f`b operator takes the results of two queries and returns only rows that appear in both result sets. For purposes of duplicate removal the `B100`F9d9INTERSECT`f`b operator does not distinguish between `B100`F9d9`F33f`_`[NULLs`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Null_(SQL)]`_`f`f`b. The `B100`F9d9INTERSECT`f`b operator removes duplicate rows from the final result set. The `B100`F9d9INTERSECT ALL`f`b operator does not remove duplicate rows from the final result set, but if a row appears X times in the first query and Y times in the second, it will appear min ( X , Y ) {\\displaystyle \\min(X,Y)} times in the result set.

>>>Example

The following example `B100`F9d9INTERSECT`f`b query returns all rows from the Orders table where Quantity is between 50 and 100.

`B100`F9d9SELECT *`f`b

`B100`F9d9FROM Orders`f`b

`B100`F9d9WHERE Quantity BETWEEN 1 AND 100`f`b

`B100`F9d9`f`b

`B100`F9d9INTERSECT`f`b

`B100`F9d9`f`b

`B100`F9d9SELECT *`f`b

`B100`F9d9FROM Orders`f`b

`B100`F9d9WHERE Quantity BETWEEN 50 AND 200;`f`b

>>EXCEPT operator

The SQL `B100`F9d9EXCEPT`f`b operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. For purposes of row elimination and duplicate removal, the `B100`F9d9EXCEPT`f`b operator does not distinguish between `B100`F9d9`F33f`_`[NULLs`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Null_(SQL)]`_`f`f`b. The `B100`F9d9EXCEPT ALL`f`b operator does not remove duplicates, but if a row appears X times in the first query and Y times in the second, it will appear max ( X − − Y , 0 ) {\\displaystyle \\max(X-Y,0)} times in the result set.

Notably, the Oracle platform provides a `B100`F9d9MINUS`f`b operator which is functionally equivalent to the `F33f`_`[SQL standard`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL:2003]`_`f `B100`F9d9EXCEPT DISTINCT`f`b operator.`:cite-ref-3[`F5bf`_`[3`#cite-note-3]`_`f]

>>>Example

The following example `B100`F9d9EXCEPT`f`b query returns all rows from the Orders table where Quantity is between 1 and 49, and those with a Quantity between 76 and 100.

Worded another way; the query returns all rows where the Quantity is between 1 and 100, apart from rows where the quantity is between 50 and 75.

`B100`F9d9SELECT *`f`b

`B100`F9d9FROM Orders`f`b

`B100`F9d9WHERE Quantity BETWEEN 1 AND 100`f`b

`B100`F9d9`f`b

`B100`F9d9EXCEPT`f`b

`B100`F9d9`f`b

`B100`F9d9SELECT *`f`b

`B100`F9d9FROM Orders`f`b

`B100`F9d9WHERE Quantity BETWEEN 50 AND 75;`f`b

>>>Example

The following example is equivalent to the above example but without using the `B100`F9d9EXCEPT`f`b operator.

`B100`F9d9SELECT o1.*`f`b

`B100`F9d9FROM (`f`b

`B100`F9d9 SELECT *`f`b

`B100`F9d9 FROM Orders`f`b

`B100`F9d9 WHERE Quantity BETWEEN 1 AND 100) o1`f`b

`B100`F9d9LEFT JOIN (`f`b

`B100`F9d9 SELECT *`f`b

`B100`F9d9 FROM Orders`f`b

`B100`F9d9 WHERE Quantity BETWEEN 50 AND 75) o2`f`b

`B100`F9d9ON o1.id = o2.id`f`b

`B100`F9d9WHERE o2.id IS NULL`f`b

>>See also

• `F33f`_`[Union (set theory)`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Union_(set_theory)]`_`f

• `F33f`_`[Join (SQL)`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Join_(SQL)]`_`f

• `F33f`_`[SQL:2003`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL:2003]`_`f

• `F33f`_`[Select (SQL)`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Select_(SQL)]`_`f

>>References

`:cite-note-1`!1.`! `F0af`_`[↑`#cite-ref-1]`_`f "The UNION [ALL], INTERSECT, MINUS Operators". Oracle. Retrieved 14 July 2016.

`:cite-note-2`!2.`! `F0af`_`[↑`#cite-ref-2]`_`f "a `B100`F9d9UNION ALL`f`b views technique for managing maintenance and performance in your large data warehouse environment ... This `B100`F9d9UNION ALL`f`b technique has saved many of my clients with issues related to time-sensitive database designs. These databases usually have an extremely volatile current timeframe, month, or day portion and the older data is rarely updated. Using different container DASD allocations, tablespaces, tables, and index definitions, the settings can be tuned for the specific performance considerations for these different volatility levels and update frequency situations." Terabyte Data Warehouse Table Design Choices - Part 2 (accessed on July 25, 2006)

`:cite-note-3`!3.`! `F0af`_`[↑`#cite-ref-3]`_`f "E071-03, `B100`F9d9EXCEPT DISTINCT`f`b table operator: Use `B100`F9d9MINUS`f`b instead of `B100`F9d9EXCEPT DISTINCT`f`b" "Oracle Compliance To Core SQL:2003". `*Docs.oracle.com`*. Retrieved 7 July 2022.

>>External links

• MSDN documentation on UNION in Transact-SQL for SQL Server

• Naming of select list items in set operations

• UNION in MySQL with Examples

• UNION in MySQL

• UNION Clause in PostgreSQL

• SQL UNION and UNION ALL

• Sort order within UNION statement

• Designing a data flow that loads a warehouse table

• Oracle 11g documentation for UNION (ALL), INTERSECT and MINUS

• SQL Set Operators

`c`F0af`_`[↑ Back to top`#top]`_`f`a